import pymysql
import easyquotation
import json
import os
from MysqlCon import MysqldbHelper


def get_now(code):
    quotation = easyquotation.use('sina')
    data = quotation.real(code)
    print(data)
    now = data[code]
    db= MysqldbHelper()

    # cursor.execute("delete from t_gp_sssj_detail where code='%s'" % (code))
    sql = "INSERT INTO `t_gp_sssj_detail`(`code`, `name`, `今开`, `昨收`, `现价`, \
        `最高`, `最低`, `买入价`, `卖出价`, `成交量股`, \
        `成交额元`, `买1量`, `买1价`, `买2量`, `买2价`, \
        `买3量`, `买3价`, `买4量`, `买4价`, `买5量`, \
        `买5价`, `卖1量`, `卖1价`, `卖2量`, `卖2价`,\
        `卖3量`, `卖3价`, `卖4量`, `卖4价`, `卖5量`,\
        `卖5价`, `日期`, `时间`) VALUES ( " + print_cs_str(33) + " )"
    params = (code, now['name'], str(now['open']), str(now['close']), str(now['now']),
              str(now['high']), str(now['low']), str(now['buy']), str(now['sell']), str(now['turnover']),
              str(now['volume']), str(now['bid1_volume']), str(now['bid1']), str(now['bid2_volume']), str(now['bid2']),
              str(now['bid3_volume']), str(now['bid3']), str(now['bid4_volume']), str(now['bid4']),
              str(now['bid5_volume']),
              str(now['bid5']), str(now['ask1_volume']), str(now['ask1']), str(now['ask2_volume']), str(now['ask2']),
              str(now['ask3_volume']), str(now['ask3']), str(now['ask4_volume']), str(now['ask4']),
              str(now['ask5_volume']),
              str(now['ask5']), now['date'], now['time'])
    db.cur.execute(sql % params)


def get_now_list(code_list):
    quotation = easyquotation.use('sina')
    data = quotation.stocks(code_list, prefix=True)
    # print(data)
    db= MysqldbHelper()
    sql = "INSERT INTO `t_gp_sssj_detail`(`code`, `name`, `今开`, `昨收`, `现价`, \
            `最高`, `最低`, `买入价`, `卖出价`, `成交量股`, \
            `成交额元`, `买1量`, `买1价`, `买2量`, `买2价`, \
            `买3量`, `买3价`, `买4量`, `买4价`, `买5量`, \
            `买5价`, `卖1量`, `卖1价`, `卖2量`, `卖2价`,\
            `卖3量`, `卖3价`, `卖4量`, `卖4价`, `卖5量`,\
            `卖5价`, `日期`, `时间`) VALUES ( " + print_cs_str(33) + " )"
    for di in data:

        now = data[di]
        print(di, round(float(now['now']) / float(now['close']) * 100 - 100,2))
        print(now)
        params = (di, now['name'], str(now['open']), str(now['close']), str(now['now']),
                  str(now['high']), str(now['low']), str(now['buy']), str(now['sell']), str(now['turnover']),
                  str(now['volume']), str(now['bid1_volume']), str(now['bid1']), str(now['bid2_volume']),
                  str(now['bid2']),
                  str(now['bid3_volume']), str(now['bid3']), str(now['bid4_volume']), str(now['bid4']),
                  str(now['bid5_volume']),
                  str(now['bid5']), str(now['ask1_volume']), str(now['ask1']), str(now['ask2_volume']),
                  str(now['ask2']),
                  str(now['ask3_volume']), str(now['ask3']), str(now['ask4_volume']), str(now['ask4']),
                  str(now['ask5_volume']),
                  str(now['ask5']), now['date'], now['time'])
        db.cur.execute(sql % params)


def query_gp_cc():
    # db = pymysql.connect("localhost", "root", "root", "lw")
    db = MysqldbHelper()

    db.cur.execute("select code from t_gp_cc")
    fund_list = db.cur.fetchall()
    code_list = []
    if fund_list:
        for row in fund_list:
            code_list.append(row[0])
    return code_list


def print_cs_str(len):
    n = 0
    s = ''
    while n < len:
        if n == len - 1:
            s += "'%s' "
        else:
            s += "'%s', "
        n += 1
    return s

def refresh_now():
    db = MysqldbHelper()

    db.cur.execute("delete from t_gp_sssj_detail")
    # get_now_list(['sh000001', 'sh000300', 'sz399001', 'sz399006'])

    get_now_list(query_gp_cc())
    # get_now_list(['300771'])
    db.close()

if __name__ == '__main__':
    # get_now('603557')
    # print(query_gp_cc())
    refresh_now()
    # get_now_list(['600036','002142','601166','601229','601288','600016','601328','601398','000001','600000'
    #               ,'601318','600519','600030','601169','600276','300059','603259','300033','000858','600048'])
